import os
import time
import chardet
from decimal import Decimal
import csv

import pandas as pd
from testcases.financialReconciliation.AliExpress_kingdee.test_collectCompareAliExpressReport import \
    create_sqlserver_database_conn
from testcases.financialReconciliation.AliExpress_kingdee.test_collectCompareAliExpressReport import exec_query_sql_more
import mysql.connector
from testcases.financialReconciliation.DifferenceKanban.comCompareTool import DataComparison
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])



def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')

class CheckData:
     #将csv文件转为列表字典
      def read_file_new(self, path: str, encode: str) -> list:
         data_list = []
         with open(path, newline='', encoding=encode) as csvfile:
             csvreader = csv.reader(csvfile, delimiter='\t')
             headers = next(csvreader)  # 读取第一行作为列标题
             for row in csvreader:
                 data = {headers[i]: row[i] for i in range(len(headers))}
                 data_list.append(data)
         #过滤空行
         data_list=list(filter(lambda x:any(x.values()),data_list))
         #去除字典中value值的空格和value值中带的逗号
         for data in data_list:
             for key in data:
                 data[key] = data[key].strip().replace(",", "")
         return data_list

      def handle_filedata(self,data:list)->dict:
          data_dict={}
          for i in data:
              if data_dict.get(i['系统订单号'])==None:
                  data_dict[i['系统订单号']]=[Decimal(i['总收入']),Decimal(i['销售收入']),Decimal(i['运费收入']),Decimal(i['退款金额']),Decimal(i['促销折扣']),
                                       Decimal(i['其他收入']),Decimal(i['交易佣金']),Decimal(i['FBA操作费']),Decimal(i['平台其他支出']),Decimal(i['广告费用']),
                                       Decimal(i['采购成本（RMB）']),Decimal(i['本地仓运费'])]
              else:
                  data_dict[i['系统订单号']]=[Decimal(i['总收入'])+data_dict[i['系统订单号']][0],Decimal(i['销售收入'])+data_dict[i['系统订单号']][1],Decimal(i['运费收入'])+data_dict[i['系统订单号']][2],
                                       Decimal(i['退款金额'])+data_dict[i['系统订单号']][3],Decimal(i['促销折扣'])+data_dict[i['系统订单号']][4],Decimal(i['其他收入'])+data_dict[i['系统订单号']][5],
                                       Decimal(i['交易佣金'])+data_dict[i['系统订单号']][6],Decimal(i['FBA操作费'])+data_dict[i['系统订单号']][7],Decimal(i['平台其他支出'])+data_dict[i['系统订单号']][8],
                                       Decimal(i['广告费用'])+data_dict[i['系统订单号']][9],Decimal(i['采购成本（RMB）'])+data_dict[i['系统订单号']][10],Decimal(i['本地仓运费'])+data_dict[i['系统订单号']][11]]
          return data_dict

      def detect_encoding(self,file_path):
          with open(file_path, 'rb') as file:
              raw_data = file.read()
              result = chardet.detect(raw_data)
              encoding = result['encoding']
          self.encoding = encoding
          return self.encoding

if __name__=='__main__':
    path='D:\zhcx-service-test\\checkdata\\11月销售成本明细-原币.csv'
    encode = CheckData().detect_encoding(path)
    data=CheckData().read_file_new(path,encode)
    business_dict=CheckData().handle_filedata(data)
    sale='''
        SELECT
           Ordercode as '系统订单号',
           ROUND( SUM ( Incomes/ExchangeRMB ), 2 ) AS '总收入',
           ROUND( SUM ( SalesPrice/ExchangeRMB ), 2 ) AS '销售收入',
           ROUND( SUM ( TransportPay/ExchangeRMB ), 2 ) AS '运费收入',
           ROUND( SUM ( SalesRefunds/ExchangeRMB ), 2 ) AS '退款金额',
           ROUND( SUM ( PromotionRebates/ExchangeRMB ), 2 ) AS '促销折扣',
           ROUND( SUM ( OtherCredits/ExchangeRMB ), 2 ) AS '其他收入',
           ROUND( SUM ( CommisionFee/ExchangeRMB ), 2 ) AS '交易佣金',
           ROUND( SUM ( FBATransactionFees/ExchangeRMB ), 2 ) AS 'FBA操作费',
           ROUND( SUM ( OrderSourceOtherDebits/ExchangeRMB ), 2 ) AS '平台其他支出',
           ROUND( SUM ( CostOfAdvertising/ExchangeRMB ), 2 ) AS '广告费用',
           ROUND( SUM ( BuyPrice ), 2 ) AS '采购成本',
           ROUND( SUM ( PackageFees/ExchangeRMB ), 2 ) AS '本地仓运费'
       FROM
           [irobotbox].[dbo].[RB_Finance_Settlement] ( NOLOCK )
       WHERE
           CustomerID = 1
       -- 	AND Ordercode = '202401291632164432404'
       -- and id<=548958243
       and CheckDate>='2023-11-01'
       and CheckDate<'2023-12-01'
       GROUP BY
           Ordercode;
    '''
    database={'erp正式库':'sqlserver|erpdb-readonly.zhcxkj.com|1433|irobotboxuser|HskwPanda047RAHRAxqktlref|irobotbox'}
    datacomp=DataComparison(database)
    value=('总收入', '销售收入', '运费收入', '退款金额', '促销折扣','其他收入','交易佣金','FBA操作费','平台其他支出','广告费用','采购成本','本地仓运费')
    keys='系统订单号'
    sale_data=datacomp.data_organization('erp正式库',sale,keys,value)
    datacomp.data_compare(business_dict,sale_data,value,1)

